-- Creates the necessary entries in PropertyDefinition, SystemConfigParameterRef, SystemConfig
-- and SystemConfigPageParameterRef in the correct in order to create working, UI editable
-- configuration parameters

CREATE PROCEDURE [dbo].[asi_InsertSystemConfig]
      @systemKeyword nvarchar(25),
      @systemConfigPageName nvarchar(30),
      @userId nvarchar(60),
      @systemConfigKey uniqueidentifier,
      @parameterName nvarchar(255),
      @propertyDefinitionKey uniqueidentifier,
      @dataTypeCode int,
      @hiControlTypeCode int,
      @hiControlWidth int,
      @description nvarchar(500),
      @defaultParameterValue nvarchar(2500),
      @sortOrder int = -1
AS
BEGIN

      DECLARE @systemEntityKey uniqueidentifier
      DECLARE @systemConfigPageKey uniqueidentifier
      DECLARE @systemUserKey uniqueidentifier
      DECLARE @organizationKey uniqueidentifier

      -- gets the system entity key for the system (module)
      SELECT @systemEntityKey = [SystemEntityKey] FROM [dbo].[SystemEntity] WHERE [SystemKeyword] = @systemKeyword

      -- Get existing page key
      SELECT @systemConfigPageKey = [SystemConfigPageKey] FROM [dbo].[SystemConfigPageRef] WHERE [SystemConfigPageName] = @systemConfigPageName

      -- Get a user key to use for the System Config variable
      SELECT @systemUserKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = @userId
      -- Provide fall-back values if UserId is not found (rather than failing on insert with cannot insert NULL)
      IF @systemUserKey IS NULL
          SELECT @systemUserKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'SYSTEM'
      IF @systemUserKey IS NULL
          SELECT @systemUserKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'ADMINISTRATOR'
      IF @systemUserKey IS NULL
          SELECT @systemUserKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'MANAGER'

      -- Get the default organization for the system config variable
      SELECT @organizationKey = [OrganizationKey] FROM [dbo].[OrganizationMain] WHERE [IsDefault] = 1

      -- create the property definition for the new parameter
      IF NOT EXISTS (SELECT 1 FROM [dbo].[PropertyDefinition] WHERE [Label] = @parameterName)
      BEGIN
            INSERT INTO [dbo].[PropertyDefinition] (
                  [PropertyDefinitionKey],
                  [Label],
                  [DataTypeCode],
                  [HIControlTypeCode],
                  [HIControlWidth])
            VALUES (
                  @propertyDefinitionKey,
                  @parameterName,
                  @dataTypeCode,
                  @hiControlTypeCode,
                  @hiControlWidth)
      END

      -- create the definition for the new parameter
      IF NOT EXISTS (SELECT 1 FROM [dbo].[SystemConfigParameterRef]
                           WHERE [ParameterName] = @parameterName AND [SystemKeyword] = @systemKeyword)
      BEGIN
            INSERT INTO [dbo].[SystemConfigParameterRef] (
                  [ParameterName],
                  [SystemKeyword],
                  [PropertyDefinitionKey],
                  [Description],
                  [DefaultParameterValue])
            VALUES (
                  @parameterName,
                  @systemKeyword,
                  @propertyDefinitionKey,
                  @description,
                  @defaultParameterValue)
      END

      -- Create the SystemConfig variable itself
      IF NOT EXISTS (SELECT 1 FROM [dbo].[SystemConfig]
                           WHERE [ParameterName] = @parameterName AND [OrganizationKey] = @organizationKey AND [SystemEntityKey] = @systemEntityKey)
      BEGIN
            INSERT INTO [dbo].[SystemConfig] (
                  [SystemConfigKey],
                  [ParameterName],
                  [ParameterValue],
                  [Description],
                  [CreatedByUserKey],
                  [CreatedOn],
                  [UpdatedByUserKey],
                  [UpdatedOn],
                  [OrganizationKey],
                  [SystemEntityKey])
            VALUES (
                  @systemConfigKey,
                  @parameterName,
                  @defaultParameterValue,
                  @description,
                  @systemUserKey,
                  GETDATE(),
                  @systemUserKey,
                  GETDATE(),
                  @organizationKey,
                  @systemEntityKey)
      END

      -- connect the parameter to the page
      IF NOT EXISTS (SELECT 1 FROM [dbo].[SystemConfigPageParameterRef]
                           WHERE [SystemConfigPageKey] = @systemConfigPageKey AND [ParameterName] = @parameterName)
      BEGIN

            -- Validate the supplied sort order
            SET @sortOrder = ISNULL(@sortOrder,-1)
            IF @sortOrder != -1
            BEGIN
                -- Check if this sort location is already in use
                  IF EXISTS (SELECT 1 FROM [dbo].[SystemConfigPageParameterRef] WHERE [SystemConfigPageKey] = @systemConfigPageKey AND [SortOrder] = @sortOrder)
                  BEGIN
                      -- Sort location is already in use, so update the existing sort orders to create a space
                      UPDATE [dbo].[SystemConfigPageParameterRef]
                         SET [SortOrder] = ISNULL([SortOrder],0) + 1
                       WHERE [SystemConfigPageKey] = @systemConfigPageKey AND ISNULL([SortOrder],0) >= @sortOrder
                  END
            END
        ELSE
            BEGIN
                -- Place the sort order at the end
                  SELECT @sortOrder = ISNULL(MAX([SortOrder]),0) + 1 FROM [dbo].[SystemConfigPageParameterRef] WHERE [SystemConfigPageKey] = @systemConfigPageKey
            END


            INSERT INTO [dbo].[SystemConfigPageParameterRef] (
                  [SystemConfigPageKey],
                  [ParameterName],
                  [SortOrder])
            VALUES (
                  @systemConfigPageKey,
                  @parameterName,
                  @sortOrder)
      END

END



GO